﻿Imports MICROFORMA.BEL
Imports MICROFORMA.DIL
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.Common

Public Class UsuarioRolDA
    Implements DIL.UsuarioRolDI



    Public Function List(ByVal IdRol As Short) As BEL.UsuarioRolBECollection Implements DIL.UsuarioRolDI.List
        Dim dr As DbDataReader = Nothing

        Dim o_BECollec_UsuarioRol_BE As New UsuarioRolBECollection

        Dim o_UsuarioRol_BE As UsuarioRolBE

        Dim db As Database = DatabaseFactory.CreateDatabase()

        'Dim query As String = "select * from RolOpcion where idrol=" & IdRol & "and estado=0"
        Dim query As String = "SELECT UsuarioRol.IdUsuario, UsuarioRol.IdRol, Usuario.Nombre, Usuario.Login," & _
                              "CASE Usuario.Estado WHEN '0' THEN 'Activo'  WHEN '1' THEN 'Suspendido'  END AS EstadoDescripcion " & _
                              "FROM Usuario INNER JOIN UsuarioRol ON Usuario.IDUsuario =UsuarioRol.idUsuario  WHERE(UsuarioRol.IdRol = " & IdRol & ")"

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        dr = db.ExecuteReader(dbCommand)

        While dr.Read()
            o_UsuarioRol_BE = New UsuarioRolBE
            o_UsuarioRol_BE.IdRol = dr("IdRol")
            o_UsuarioRol_BE.Login = dr("Login")
            o_UsuarioRol_BE.Nombre = dr("Nombre")
            o_UsuarioRol_BE.IdUsuario = dr("IdUsuario")

            o_BECollec_UsuarioRol_BE.Add(o_UsuarioRol_BE)
            o_UsuarioRol_BE = Nothing
        End While

        Return o_BECollec_UsuarioRol_BE
    End Function

    Public Function GetList(ByVal IdRol As Short) As DataTable

        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim query As String = "SELECT UsuarioRol.IdUsuario, UsuarioRol.IdRol, Usuario.Nombre, Usuario.Login," & _
                              "CASE Usuario.Estado WHEN '0' THEN 'Activo'  WHEN '1' THEN 'Suspendido'  END AS EstadoDescripcion " & _
                              "FROM Usuario INNER JOIN UsuarioRol ON Usuario.IDUsuario =UsuarioRol.idUsuario  WHERE(UsuarioRol.IdRol = " & IdRol & ")"

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)


        Return productsDataSet.Tables(0)

    End Function

    Public Function Add(ByVal usuario_Rol As BEL.UsuarioRolBE) As String Implements DIL.UsuarioRolDI.Add
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Try
            Dim query As String = "Insert into UsuarioRol (IdUsuario,IdRol,FechaCreacion)" _
                                    & "Values (" & usuario_Rol.IdUsuario & ",'" & usuario_Rol.IdRol _
                                    & "'," & "getdate()" & ")"


            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function Delete(ByVal IdUsuario As Short, ByVal IdRol As Short) As String Implements DIL.UsuarioRolDI.Delete
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Try
            Dim query As String = "Delete from UsuarioRol where IdUsuario=" & IdUsuario & " and IdRol='" & IdRol & "'"

            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)

            db.ExecuteNonQuery(o_dbCommand)

            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function
End Class
